/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.home.dao;

import com.home.common.Constants;
import com.home.common.utils.HibernateUtil;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 *
 * @author vi
 */
public class AttributeDao extends BaseDao {
    public List<Object[]> findPerOfAttrReportWeek(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int year, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, attribute_group.attribute_group_name, attribute.attribute_name, (AVG(scheme.`VALUE`))attribute_avg_score ");
            } else {
                queryStr.append("SELECT agent.agent_name, attribute_group.attribute_group_name, attribute.attribute_name, (AVG(scheme.`VALUE`))attribute_avg_score ");
            }
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("LEFT JOIN scorecard_details ON scorecard_details.fk_scorecard = scorecard.id_scorecard ");
            queryStr.append("LEFT JOIN scheme ON scheme.id_scheme = scorecard_details.fk_scheme_details ");
            queryStr.append("LEFT JOIN attribute ON scorecard_details.fk_attribute = attribute.id_attribute ");
            queryStr.append("LEFT JOIN attribute_group ON attribute.fk_attribute_group = attribute_group.id_attribute_group ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.week BETWEEN :periodFrom AND :periodTo) ");            
            queryStr.append("AND (scheme.`VALUE` IS NOT NULL) ");
            queryStr.append("AND (scorecard.year = :year) ");
            queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (scorecard.fk_subject IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_subject, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else {
                queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_AGENT, attribute_group.id_attribute_group, attribute.id_attribute ");
            }

            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, attribute_group.`ORDER`,attribute.`ORDER` ASC");
            } else {
                queryStr.append("ORDER BY agent.agent_name,attribute_group.`ORDER`,attribute.`ORDER` ASC");
            }            

            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("year", year);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            query.setParameter("chanelId", chanelId);

            if (query.list().size() > 0) {
                list = query.list();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }

        return list;
    }
    
    public List<Object[]> findPerOfAttrReportMonth(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, attribute_group.attribute_group_name, attribute.attribute_name, (AVG(scheme.`VALUE`))attribute_avg_score ");
            } else {
                queryStr.append("SELECT agent.agent_name, attribute_group.attribute_group_name, attribute.attribute_name, (AVG(scheme.`VALUE`))attribute_avg_score ");
            }
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("LEFT JOIN scorecard_details ON scorecard_details.fk_scorecard = scorecard.id_scorecard ");
            queryStr.append("LEFT JOIN scheme ON scheme.id_scheme = scorecard_details.fk_scheme_details ");
            queryStr.append("LEFT JOIN attribute ON scorecard_details.fk_attribute = attribute.id_attribute ");
            queryStr.append("LEFT JOIN attribute_group ON attribute.fk_attribute_group = attribute_group.id_attribute_group ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.month BETWEEN :periodFrom AND :periodTo) ");            
            queryStr.append("AND (scheme.`VALUE` IS NOT NULL) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) "); 
            queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (scorecard.fk_subject IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_subject, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else {
                queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_AGENT, attribute_group.id_attribute_group, attribute.id_attribute ");
            }

            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, attribute_group.`ORDER`,attribute.`ORDER` ASC");
            } else {
                queryStr.append("ORDER BY agent.agent_name,attribute_group.`ORDER`,attribute.`ORDER` ASC");
            }

            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            query.setParameter("chanelId", chanelId);

            if (query.list().size() > 0) {
                list = query.list();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }

        return list;
    }
    
    public List<Object[]> findPerOfAttrReportQuarter(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, attribute_group.attribute_group_name, attribute.attribute_name, (AVG(scheme.`VALUE`))attribute_avg_score ");
            } else {
                queryStr.append("SELECT agent.agent_name, attribute_group.attribute_group_name, attribute.attribute_name, (AVG(scheme.`VALUE`))attribute_avg_score ");
            }
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("LEFT JOIN scorecard_details ON scorecard_details.fk_scorecard = scorecard.id_scorecard ");
            queryStr.append("LEFT JOIN scheme ON scheme.id_scheme = scorecard_details.fk_scheme_details ");
            queryStr.append("LEFT JOIN attribute ON scorecard_details.fk_attribute = attribute.id_attribute ");
            queryStr.append("LEFT JOIN attribute_group ON attribute.fk_attribute_group = attribute_group.id_attribute_group ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.quarter BETWEEN :periodFrom AND :periodTo) ");            
            queryStr.append("AND (scheme.`VALUE` IS NOT NULL) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) "); 
            queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (scorecard.fk_subject IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_subject, attribute_group.id_attribute_group, attribute.id_attribute ");
            } else {
                queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_AGENT, attribute_group.id_attribute_group, attribute.id_attribute ");
            }

            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, attribute_group.`ORDER`,attribute.`ORDER` ASC");
            } else {
                queryStr.append("ORDER BY agent.agent_name,attribute_group.`ORDER`,attribute.`ORDER` ASC");
            }

            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            query.setParameter("chanelId", chanelId);

            if (query.list().size() > 0) {
                list = query.list();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }

        return list;
    }
}
